package util;

import entity.ColumnModel;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DBUtil extends ConfigUtil {
    private static Connection con = null;
    private static ResultSet rs = null;
    private static Statement statement = null;

    protected static String lineToCamel(String source) {
        if (source == null || source.indexOf("_") == -1) return source;
        StringBuffer sb = new StringBuffer();
        Matcher matcher = Pattern.compile("(_)([a-zA-Z])").matcher(source);
        int last = 0;
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(2).toUpperCase());
            last = matcher.end();
        }
        sb.append(source, last, source.length());
        return sb.toString();
    }



    protected static String lineToCamelCdf(String source) {
        source = StringUtil.toLowerFirstChar(source.replace("ID", "Id"));

        if (source == null || source.indexOf("_") == -1) {
//            source
            source = source.replace("cOMMENT", "comment")
                    .replace("lENGTH", "length");
            return source;
        }
        StringBuffer sb = new StringBuffer();
        Matcher matcher = Pattern.compile("(_)([a-zA-Z])").matcher(source);
        int last = 0;
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(2).toUpperCase());
            last = matcher.end();
        }
        sb.append(source, last, source.length());
        return sb.toString();
    }

    public static List<Map<String, String>> getDBSchema(String table) {
        List<Map<String, String>> list = new ArrayList<>();
        String sql = "SELECT a.COLUMN_NAME,a.DATA_TYPE,a.COLUMN_KEY,a.COLUMN_COMMENT FROM information_schema.`COLUMNS` a " +
                "WHERE a.TABLE_SCHEMA=(SELECT DATABASE() FROM DUAL) AND a.TABLE_NAME='" + table + "'";
        try {
            rs = executeSql(sql);
            while (rs.next()) {
                Map<String, String> m = new HashMap<>();
                String sqlColumn = rs.getString("COLUMN_NAME");
                m.put("sqlColumn", sqlColumn);
                m.put("javaColumn", lineToCamelCdf(sqlColumn));
                m.put("dataType", rs.getString("DATA_TYPE"));
                String comment = rs.getString("COLUMN_COMMENT");
                if (comment.contains("：")) {
                    comment = comment.replace("：", ":").replaceAll("\n\\s*", "-");
                    if (comment.contains("-")) {
                        String[] split = comment.split(":", 2);
                        String s = split[1];
                        if (s.startsWith("-")) s = s.substring(1);
                        String[] lineSplit = s.split("-");
                        StringBuffer sb = new StringBuffer(split[0] + ":");
                        for (int i = 0; i < lineSplit.length; i++) {
                            sb.append(i).append("-").
                                    append(lineSplit[i]).append(",");
                        }
                        comment = sb.deleteCharAt(sb.length() - 1).toString();
                    }
                } else comment = comment.replaceAll("\n\\s*", ",");
                m.put("comment", comment);
                m.put("primary", rs.getString("COLUMN_KEY"));
                list.add(m);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return list;
    }

    public static List<Map<String, String>> getTables(String tables) {
        List<Map<String, String>> list = new ArrayList<>();
        String sql = "SELECT a.TABLE_NAME,a.`TABLE_COMMENT` FROM information_schema.`tables` a " +
                "WHERE a.TABLE_SCHEMA=(SELECT DATABASE() FROM DUAL)";

        if (!StringUtil.isEmpty(tables)) {
            tables = "\"" + tables.replaceAll(",", "\",\"") + "\"";
            sql += " AND a.TABLE_NAME IN (" + tables + ")";
        }
        try {
            rs = executeSql(sql);
            while (rs.next()) {
                Map<String, String> m = new HashMap<>();
                m.put("tableName", rs.getString("TABLE_NAME"));
                m.put("title", rs.getString("TABLE_COMMENT"));
                list.add(m);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return list;
    }

    private static void closeConnection() {
        try {
            if (statement != null) statement.close();
            if (rs != null) rs.close();
            if (con != null) con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static ResultSet executeSql(String sql) throws Exception {
        if (con == null || con.isClosed()) con = getConnection();
        statement = con.createStatement();
        return statement.executeQuery(sql);
    }

    private static Connection getConnection() throws Exception {
        Class.forName(StringUtil.getDefault(bm.getDriverClass(), "com.mysql.jdbc.Driver")).newInstance();
        return DriverManager.getConnection(String.format("%s/%s%s", bm.getHost(), bm.getDb(), "?useUnicode=true&characterEncoding=utf-8"), bm.getUserName(), bm.getPwd());
    }

    protected static String ascii2Native(String str) {
        StringBuilder sb = new StringBuilder();
        int begin = 0;
        int index = str.indexOf("\\u");
        while (index != -1) {
            sb.append(str.substring(begin, index));
            sb.append(ascii2Char(str.substring(index, index + 6)));
            begin = index + 6;
            index = str.indexOf("\\u", begin);
        }
        sb.append(str.substring(begin));
        return sb.toString();
    }

    private static char ascii2Char(String str) {
        if (str.length() != 6) {
            throw new IllegalArgumentException(
                    "Ascii string of a native character must be 6 character.");
        }
        if (!"\\u".equals(str.substring(0, 2))) {
            throw new IllegalArgumentException(
                    "Ascii string of a native character must start with \"\\u\".");
        }
        String tmp = str.substring(2, 4);
        int code = Integer.parseInt(tmp, 16) << 8;
        tmp = str.substring(4, 6);
        code += Integer.parseInt(tmp, 16);
        return (char) code;
    }

    public static String component(String comment) {
        String[] splits = null;
        StringBuffer sb = new StringBuffer();
        if (comment.contains("|")) {
            if (comment.startsWith("-")) comment = comment.substring(1);
            String[] lineSplit = comment.split("\\|");
            for (int i = 0; i < lineSplit.length; i++) {
                sb.append("{id:").append(i).
                        append(",text:'").append(lineSplit[i]).append("'},\n");
            }

        } else {
            splits = comment.split(",");
            for (String split : splits) {
                sb.append("{id:").append(split.replaceAll("(\\d+)-*(.*)", "$1")).
                        append(",text:'").append(split.replaceAll("(\\d+)-*(.*)", "$2")).append("'},\n");
            }
        }
        return "[\n" + sb.deleteCharAt(sb.lastIndexOf(",")).toString() + "]}";
    }

    public static String renderer(String comment) {
        if (StringUtil.isEmpty(comment)) return "";
        if ("remark".equals(comment) || "comment".equals(comment)) return ", renderer: function (v) {" +
                "                                if (v) v = \"<span title='\" + v + \"'>\" + v + \"<span>\";" +
                "                                return v;" +
                "                            }";
        if (comment.toLowerCase().endsWith("amount") || comment.toLowerCase().endsWith("price"))

            return ", renderer: Ext.util.Format.money";
        if (comment.endsWith("Date")) {

            return ", renderer: Ext.util.Format.dateRenderer('Y-m-d')";
        }
        if (comment.endsWith("Date") || comment.endsWith("Time")) {

            return ", renderer: Ext.util.Format.dateRenderer('Y-m-d H:i:s')";
        }
        comment = comment.replaceAll("\\d-*", "");
        return ", renderer: function (v) {" +
                "                                return '" + comment + "'.split(',')[v]||'';" +
                "                            }";
    }

    public static String componentModel(ColumnModel m) {
        String comment = m.getComment();
        String[] splits = null;
        StringBuffer sb = new StringBuffer();
        splits = comment.split(",");
        for (String split : splits) {
            sb.append("{id:").append(split.replaceAll("(\\d+)-*(.*)", "$1")).
                    append(",text:'").append(split.replaceAll("(\\d+)-*(.*)", "$2")).append("'},\n");
        }

        return "[\n" + sb.deleteCharAt(sb.lastIndexOf(",")).toString() + "]}";
    }

}
